set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table bidefault.dm_waybill_collect_intime
select a.taking_date
       , a.waybill
       , a.is_interrupted
       , a.truncated_time
       , a.receive_city_id
       , a.receive_city
       , a.region_code
       , a.region_name
       , a.receive_proxy_code
       , a.receive_proxy
       , a.receive_franchisee_code
       , a.receive_franchisee
       , a.receive_network_code
       , a.receive_network_name
       , a.actual_collect_code
       , a.actual_collect_name
       , a.plan_collect_code
       , a.plan_collect_name
       , a.actual_center_code
       , a.actual_center_name
       , a.collect_proxy_code
       , a.collect_proxy
       , a.is_diff_proxy
       , a.actual_collect_time
       , a.actual_send_time
       , a.actual_collect_arr_time
       , a.plan_collect_arr_time
       , a.actual_center_arr_time
       , a.hand_over_latest
       , a.is_arr_collect_intime
       , a.is_arr_center_intime
       , a.hand_over_intime
       , a.collect_responsible_code
       , a.collect_responsible
       , a.hand_over_responsible_code
       , a.hand_over_responsible
       , a.type
       , a.order_source
       , a.goods_type_code
       , a.goods_type_name
       , a.end_code
       , a.end_name
       , a.plan_arr_date
       , a.plan_center_arr_time
       , a.is_over
       , a.shipment_no
       , a.customer_code
       , a.total_days_t
       , a.center_planned_departure
       , a.is_intime
       , a.express_type_code
       , a.express_type_name
       , a.last_area_code
       , a.last_area_name
       , a.last_city_code
       , a.last_city_name
       , a.last_agent_code
       , a.last_agent_name
       , a.first_nodal_send_time
       , a.first_nodal_arrival_time
       , a.first_center_send_time
       , a.first_center_arrival_time
       , a.if_yiqin_late
       , a.if_route_late
       , a.if_center_send_timely
       , a.customer_name
       , a.first_nodal_agent_code
       , a.first_nodal_agent_name
       , a.first_nodal_fran_code
       , a.first_nodal_fran_name
       , a.shipment_shifts
       , a.taking_user_code
       , a.taking_user_name
       , a.actual_network_shipment_no
       , a.actual_nodal_shipment_no
       , a.first_network_plan_send_time
       , a.first_nodal_plan_send_time
       , a.hand_over_duty_flag
       , dim_network.area_code          as receive_area_code
       , dim_network.area_name          as receive_area_name
       , bagging.scan_time              as first_nodal_bagging_time
       , a.order_mark_en
       , a.order_mark_zh
       , a.taking_date  as dt
from (
    select
          to_date(early_scan_time)      as taking_date                    --揽收日期
         ,waybill_no                    as waybill                        --运单号
         ,is_interrupted                as is_interrupted                 --是否截单0否1是2其他
         ,truncated_time                as truncated_time                 --截单时间
         ,receive_city_id               as receive_city_id                --收件城市id
         ,receive_city                  as receive_city                   --收件城市
         ,region_code                   as region_code                    --收件管理大区编码
         ,region_name                   as region_name                    --收件管理大区名称
         ,receive_proxy_code            as receive_proxy_code             --收件代理区编码
         ,receive_proxy                 as receive_proxy                  --收件代理区名称
         ,receive_franchisee_code       as receive_franchisee_code        --收件加盟商编码
         ,receive_franchisee            as receive_franchisee             --收件加盟商名称
         ,receive_network_code          as receive_network_code           --收件网点编码
         ,receive_network_name          as receive_network_name           --收件网点名称
         ,actual_collect_code           as actual_collect_code            --实际交件集散点编码
         ,actual_collect_name           as actual_collect_name            --实际交件集散点名称
         ,plan_collect_code             as plan_collect_code              --规划到达集散点编码
         ,plan_collect_name             as plan_collect_name              --规划到达集散点名称
         ,actual_center_code            as actual_center_code             --实际交件转运中心编码
         ,actual_center_name            as actual_center_name             --实际交件转运中心名称
         ,collect_proxy_code            as collect_proxy_code             --交件代理区编码
         ,collect_proxy                 as collect_proxy                  --交件代理区名称
         ,is_diff_proxy                 as is_diff_proxy                  --是否跨代理区交件
         ,early_scan_time               as actual_collect_time            --实际收件时间
         ,actual_send_time              as actual_send_time               --网点实际发件时间
         ,actual_collect_arr_time       as actual_collect_arr_time        --实际交件集散点时间
         ,null                          as plan_collect_arr_time          --规划到达集散点时间
         ,actual_center_arr_time        as actual_center_arr_time         --实际首中心到车时间
         ,null                          as hand_over_latest               --交接最晚到车时间
         ,null                          as is_arr_collect_intime          --到达集散是否准点
         ,null                          as is_arr_center_intime           --到达集散是否准点
         ,null                          as hand_over_intime               --交接是否准点
         ,null                          as collect_responsible_code       --交件不准点责任所属机构编码
         ,null                          as collect_responsible            --交件不准点责任所属机构
         ,null                          as hand_over_responsible_code     --交接不准点责任所属机构编码
         ,null                          as hand_over_responsible          --交接不准点责任所属机构
         ,type                          as type                           --类型,1:无三段码2:无路由3:错发4规划问题5正常6其他
         ,order_source                  as order_source                   --订单来源1紫金山2逍遥峰3桃花岛4其他
         ,goods_type_code               as goods_type_code                --物品类型编码
         ,goods_type_name               as goods_type_name                --物品类型名称
         ,last_network_code             as end_code                       --目的网点编码
         ,last_network_name             as end_name                       --目的网点名称
         ,null                          as plan_arr_date                  --规划到件日期
         ,null                          as plan_center_arr_time           --规划首中心到车时间
         ,null                          as is_over
         ,actual_center_shipment_no     as shipment_no                    --实际首中心到件任务号     (20220903新增)
         ,customer_code                 as customer_code                  --客户编号                 (20220903新增)
         ,null                          as total_days_t                   --T+x时效                  (20220903新增)
         ,null                          as center_planned_departure       --中心规划发车时间-1小时   (20220903新增)
         ,null                          as is_intime                      --时效是否准点             (20220903新增)
         ,express_type_code             as express_type_code              --产品类型编码             (20221103新增)
         ,express_type_name             as express_type_name              --产品类型名称             (20221103新增)
         ,last_area_code                as last_area_code                 --目的区/县编码            (20221113新增)
         ,last_area_name                as last_area_name                 --目的区/县名称            (20221113新增)
         ,last_city_code                as last_city_code                 --目的城市编码             (20221113新增)
         ,last_city_name                as last_city_name                 --目的城市名称             (20221113新增)
         ,last_agent_code               as last_agent_code                --目的代理区编码           (20221113新增)
         ,last_agent_name               as last_agent_name                --目的代理区名称           (20221113新增)
         ,first_nodal_send_time         as first_nodal_send_time          --始发集散发件扫描时间     (20221113新增)
         ,first_nodal_arrival_time      as first_nodal_arrival_time       --始发集散到件扫描时间     (20221113新增)
         ,first_center_send_time        as first_center_send_time         --始发转运中心发件扫描时间 (20221113新增)
         ,first_center_arrival_time     as first_center_arrival_time      --始发转运中心到件扫描时间 (20221113新增)
         ,if_yiqin_late                 as if_yiqin_late                  --是否疫情件               (20221213新增)
         ,null                          as if_route_late                  --路由晚点                 (20221213新增)
         ,null                          as if_center_send_timely          --是否交件晚点及时发出1:是,0:否(20221213新增)
         ,customer_name                 as customer_name                  --客户名称
         ,first_nodal_agent_code        as first_nodal_agent_code         --实际交件集散点代理区编码
         ,first_nodal_agent_name        as first_nodal_agent_name         --实际交件集散点代理区名称
         ,first_nodal_fran_code         as first_nodal_fran_code          --实际交件集散点加盟商编码
         ,first_nodal_fran_name         as first_nodal_fran_name          --实际交件集散点加盟商名称
         ,actual_center_shipment_shifts as shipment_shifts                --实际交件支线任务班次 20230420新增
         ,taking_user_code              as taking_user_code               --揽收业务员 20230516
         ,taking_user_name              as taking_user_name               --揽收业务员 20230516
         ,actual_network_shipment_no    as actual_network_shipment_no     --网点发件任务单
         ,actual_nodal_shipment_no      as actual_nodal_shipment_no       --集散到件发件任务单
         ,null                          as first_network_plan_send_time   --始发网点规划发车时间
         ,null                          as first_nodal_plan_send_time     --始发集散规划发车时间
         ,null                          as hand_over_duty_flag            --不准点责任标识
         ,order_mark_en                 as order_mark_en                  --订单标签英文
         ,order_mark_zh                 as order_mark_zh                  --订单标签中文
         -- ,to_date(early_scan_time)      as dt                             --揽收日期
    from bidefault.dwd_first_route_for_submit
    where type in (1,2,3)  --三段码问题、规划问题、自收自派问题
    union all
    select
          to_date(early_scan_time)      as taking_date                    --揽收日期
         ,waybill_no                    as waybill                        --运单号
         ,is_interrupted                as is_interrupted                 --是否截单0否1是2其他
         ,truncated_time                as truncated_time                 --截单时间
         ,receive_city_id               as receive_city_id                --收件城市id
         ,receive_city                  as receive_city                   --收件城市
         ,region_code                   as region_code                    --收件管理大区编码
         ,region_name                   as region_name                    --收件管理大区名称
         ,receive_proxy_code            as receive_proxy_code             --收件代理区编码
         ,receive_proxy                 as receive_proxy                  --收件代理区名称
         ,receive_franchisee_code       as receive_franchisee_code        --收件加盟商编码
         ,receive_franchisee            as receive_franchisee             --收件加盟商名称
         ,receive_network_code          as receive_network_code           --收件网点编码
         ,receive_network_name          as receive_network_name           --收件网点名称
         ,actual_collect_code           as actual_collect_code            --实际交件集散点编码
         ,actual_collect_name           as actual_collect_name            --实际交件集散点名称
         ,plan_collect_code             as plan_collect_code              --规划到达集散点编码
         ,plan_collect_name             as plan_collect_name              --规划到达集散点名称
         ,actual_center_code            as actual_center_code             --实际交件转运中心编码
         ,actual_center_name            as actual_center_name             --实际交件转运中心名称
         ,collect_proxy_code            as collect_proxy_code             --交件代理区编码
         ,collect_proxy                 as collect_proxy                  --交件代理区名称
         ,is_diff_proxy                 as is_diff_proxy                  --是否跨代理区交件
         ,early_scan_time               as actual_collect_time            --实际收件时间
         ,actual_send_time              as actual_send_time               --网点实际发件时间
         ,actual_collect_arr_time       as actual_collect_arr_time        --实际交件集散点时间
         ,null                          as plan_collect_arr_time          --规划到达集散点时间
         ,actual_center_arr_time        as actual_center_arr_time         --实际首中心到车时间
         ,null                          as hand_over_latest               --交接最晚到车时间
         ,null                          as is_arr_collect_intime          --到达集散是否准点
         ,null                          as is_arr_center_intime           --到达集散是否准点
         ,null                          as hand_over_intime               --交接是否准点
         ,null                          as collect_responsible_code       --交件不准点责任所属机构编码
         ,null                          as collect_responsible            --交件不准点责任所属机构
         ,null                          as hand_over_responsible_code     --交接不准点责任所属机构编码
         ,null                          as hand_over_responsible          --交接不准点责任所属机构
         ,type                          as type                           --类型,1:无三段码2:无路由3:错发4规划问题5正常6其他
         ,order_source                  as order_source                   --订单来源1紫金山2逍遥峰3桃花岛4其他
         ,goods_type_code               as goods_type_code                --物品类型编码
         ,goods_type_name               as goods_type_name                --物品类型名称
         ,last_network_code             as end_code                       --目的网点编码
         ,last_network_name             as end_name                       --目的网点名称
         ,null                          as plan_arr_date                  --规划到件日期
         ,null                          as plan_center_arr_time           --规划首中心到车时间
         ,null                          as is_over
         ,actual_center_shipment_no     as shipment_no                    --实际首中心到件任务号     (20220903新增)
         ,customer_code                 as customer_code                  --客户编号                 (20220903新增)
         ,null                          as total_days_t                   --T+x时效                  (20220903新增)
         ,null                          as center_planned_departure       --中心规划发车时间-1小时   (20220903新增)
         ,null                          as is_intime                      --时效是否准点             (20220903新增)
         ,express_type_code             as express_type_code              --产品类型编码             (20221103新增)
         ,express_type_name             as express_type_name              --产品类型名称             (20221103新增)
         ,last_area_code                as last_area_code                 --目的区/县编码            (20221113新增)
         ,last_area_name                as last_area_name                 --目的区/县名称            (20221113新增)
         ,last_city_code                as last_city_code                 --目的城市编码             (20221113新增)
         ,last_city_name                as last_city_name                 --目的城市名称             (20221113新增)
         ,last_agent_code               as last_agent_code                --目的代理区编码           (20221113新增)
         ,last_agent_name               as last_agent_name                --目的代理区名称           (20221113新增)
         ,first_nodal_send_time         as first_nodal_send_time          --始发集散发件扫描时间     (20221113新增)
         ,first_nodal_arrival_time      as first_nodal_arrival_time       --始发集散到件扫描时间     (20221113新增)
         ,first_center_send_time        as first_center_send_time         --始发转运中心发件扫描时间 (20221113新增)
         ,first_center_arrival_time     as first_center_arrival_time      --始发转运中心到件扫描时间 (20221113新增)
         ,if_yiqin_late                 as if_yiqin_late                  --是否疫情件               (20221213新增)
         ,null                          as if_route_late                  --路由晚点                 (20221213新增)
         ,null                          as if_center_send_timely          --是否交件晚点及时发出1:是,0:否(20221213新增)
         ,customer_name                 as customer_name                  --客户名称
         ,first_nodal_agent_code        as first_nodal_agent_code         --实际交件集散点代理区编码
         ,first_nodal_agent_name        as first_nodal_agent_name         --实际交件集散点代理区名称
         ,first_nodal_fran_code         as first_nodal_fran_code          --实际交件集散点加盟商编码
         ,first_nodal_fran_name         as first_nodal_fran_name          --实际交件集散点加盟商名称
         ,actual_center_shipment_shifts as shipment_shifts                --实际交件支线任务班次 20230420新增
         ,taking_user_code              as taking_user_code               --揽收业务员 20230516
         ,taking_user_name              as taking_user_name               --揽收业务员 20230516
         ,actual_network_shipment_no    as actual_network_shipment_no     --网点发件任务单
         ,actual_nodal_shipment_no      as actual_nodal_shipment_no       --集散到件发件任务单
         ,null                          as first_network_plan_send_time   --始发网点规划发车时间
         ,null                          as first_nodal_plan_send_time     --始发集散规划发车时间
         ,null                          as hand_over_duty_flag            --不准点责任标识
         ,order_mark_en                 as order_mark_en                  --订单标签英文
         ,order_mark_zh                 as order_mark_zh                  --订单标签中文
         -- ,to_date(early_scan_time)      as dt                             --揽收日期
    from bidefault.dwd_first_route_city_for_submit
    where type = 4  --同城件数据打标
    union all
    select
          to_date(early_scan_time)       as taking_date                    --揽收日期
         ,waybill_no                    as waybill                        --运单号
         ,is_interrupted                as is_interrupted                 --是否截单0否1是2其他
         ,truncated_time                as truncated_time                 --截单时间
         ,receive_city_id               as receive_city_id                --收件城市id
         ,receive_city                  as receive_city                   --收件城市
         ,region_code                   as region_code                    --收件管理大区编码
         ,region_name                   as region_name                    --收件管理大区名称
         ,receive_proxy_code            as receive_proxy_code             --收件代理区编码
         ,receive_proxy                 as receive_proxy                  --收件代理区名称
         ,receive_franchisee_code       as receive_franchisee_code        --收件加盟商编码
         ,receive_franchisee            as receive_franchisee             --收件加盟商名称
         ,receive_network_code          as receive_network_code           --收件网点编码
         ,receive_network_name          as receive_network_name           --收件网点名称
         ,actual_collect_code           as actual_collect_code            --实际交件集散点编码
         ,actual_collect_name           as actual_collect_name            --实际交件集散点名称
         ,plan_collect_code             as plan_collect_code              --规划到达集散点编码
         ,plan_collect_name             as plan_collect_name              --规划到达集散点名称
         ,actual_center_code            as actual_center_code             --实际交件转运中心编码
         ,actual_center_name            as actual_center_name             --实际交件转运中心名称
         ,collect_proxy_code            as collect_proxy_code             --交件代理区编码
         ,collect_proxy                 as collect_proxy                  --交件代理区名称
         ,is_diff_proxy                 as is_diff_proxy                  --是否跨代理区交件
         ,early_scan_time               as actual_collect_time            --实际收件时间
         ,actual_send_time              as actual_send_time               --网点实际发件时间
         ,actual_collect_arr_time       as actual_collect_arr_time        --实际交件集散点时间
         ,null                          as plan_collect_arr_time          --规划到达集散点时间
         ,actual_center_arr_time        as actual_center_arr_time         --实际首中心到车时间
         ,null                          as hand_over_latest               --交接最晚到车时间
         ,null                          as is_arr_collect_intime          --到达集散是否准点
         ,null                          as is_arr_center_intime           --到达中心是否准点
         ,null                          as hand_over_intime               --交接是否准点
         ,null                          as collect_responsible_code       --交件不准点责任所属机构编码
         ,null                          as collect_responsible            --交件不准点责任所属机构
         ,null                          as hand_over_responsible_code     --交接不准点责任所属机构编码
         ,null                          as hand_over_responsible          --交接不准点责任所属机构
         ,type                          as type                           --类型,1:无三段码2:无路由3:错发4规划问题5正常6其他
         ,order_source                  as order_source                   --订单来源1紫金山2逍遥峰3桃花岛4其他
         ,goods_type_code               as goods_type_code                --物品类型编码
         ,goods_type_name               as goods_type_name                --物品类型名称
         ,last_network_code             as end_code                       --目的网点编码
         ,last_network_name             as end_name                       --目的网点名称
         ,null                          as plan_arr_date                  --规划到件日期
         ,null                          as plan_center_arr_time           --规划首中心到车时间
         ,null                          as is_over
         ,actual_center_shipment_no     as shipment_no                    --实际首中心到件任务号     (20220903新增)
         ,customer_code                 as customer_code                  --客户编号                 (20220903新增)
         ,null                          as total_days_t                   --T+x时效                  (20220903新增)
         ,null                          as center_planned_departure       --中心规划发车时间-1小时   (20220903新增)
         ,null                          as is_intime                      --时效是否准点             (20220903新增)
         ,express_type_code             as express_type_code              --产品类型编码             (20221103新增)
         ,express_type_name             as express_type_name              --产品类型名称             (20221103新增)
         ,last_area_code                as last_area_code                 --目的区/县编码            (20221113新增)
         ,last_area_name                as last_area_name                 --目的区/县名称            (20221113新增)
         ,last_city_code                as last_city_code                 --目的城市编码             (20221113新增)
         ,last_city_name                as last_city_name                 --目的城市名称             (20221113新增)
         ,last_agent_code               as last_agent_code                --目的代理区编码           (20221113新增)
         ,last_agent_name               as last_agent_name                --目的代理区名称           (20221113新增)
         ,first_nodal_send_time         as first_nodal_send_time          --始发集散发件扫描时间     (20221113新增)
         ,first_nodal_arrival_time      as first_nodal_arrival_time       --始发集散到件扫描时间     (20221113新增)
         ,first_center_send_time        as first_center_send_time         --始发转运中心发件扫描时间 (20221113新增)
         ,first_center_arrival_time     as first_center_arrival_time      --始发转运中心到件扫描时间 (20221113新增)
         ,if_yiqin_late                 as if_yiqin_late                  --是否疫情件               (20221213新增)
         ,null                          as if_route_late                  --路由晚点                 (20221213新增)
         ,null                          as if_center_send_timely          --是否交件晚点及时发出1:是,0:否(20221213新增)
         ,customer_name                 as customer_name                  --客户名称
         ,first_nodal_agent_code        as first_nodal_agent_code         --实际交件集散点代理区编码
         ,first_nodal_agent_name        as first_nodal_agent_name         --实际交件集散点代理区名称
         ,first_nodal_fran_code         as first_nodal_fran_code          --实际交件集散点加盟商编码
         ,first_nodal_fran_name         as first_nodal_fran_name          --实际交件集散点加盟商名称
         ,actual_center_shipment_shifts as shipment_shifts                --实际交件支线任务班次 20230420新增
         ,taking_user_code              as taking_user_code               --揽收业务员 20230516
         ,taking_user_name              as taking_user_name               --揽收业务员 20230516
         ,actual_network_shipment_no    as actual_network_shipment_no     --网点发件任务单
         ,actual_nodal_shipment_no      as actual_nodal_shipment_no       --集散到件发件任务单
         ,null                          as first_network_plan_send_time   --始发网点规划发车时间
         ,null                          as first_nodal_plan_send_time     --始发集散规划发车时间
         ,null                          as hand_over_duty_flag            --不准点责任标识
         ,order_mark_en                 as order_mark_en                  --订单标签英文
         ,order_mark_zh                 as order_mark_zh                  --订单标签中文
         -- ,to_date(early_scan_time)      as dt                             --揽收日期
    from bidefault.dwd_second_error_route_for_submit
    where type in (5,4)   --错发问题
    union all
    select
          to_date(early_scan_time)      as taking_date                    --揽收日期
         ,waybill_no                    as waybill                        --运单号
         ,is_interrupted                as is_interrupted                 --是否截单0否1是2其他
         ,truncated_time                as truncated_time                 --截单时间
         ,receive_city_id               as receive_city_id                --收件城市id
         ,receive_city                  as receive_city                   --收件城市
         ,region_code                   as region_code                    --收件管理大区编码
         ,region_name                   as region_name                    --收件管理大区名称
         ,receive_proxy_code            as receive_proxy_code             --收件代理区编码
         ,receive_proxy                 as receive_proxy                  --收件代理区名称
         ,receive_franchisee_code       as receive_franchisee_code        --收件加盟商编码
         ,receive_franchisee            as receive_franchisee             --收件加盟商名称
         ,receive_network_code          as receive_network_code           --收件网点编码
         ,receive_network_name          as receive_network_name           --收件网点名称
         ,actual_collect_code           as actual_collect_code            --实际交件集散点编码
         ,actual_collect_name           as actual_collect_name            --实际交件集散点名称
         ,plan_collect_code             as plan_collect_code              --规划到达集散点编码
         ,plan_collect_name             as plan_collect_name              --规划到达集散点名称
         ,actual_center_code            as actual_center_code             --实际交件转运中心编码
         ,actual_center_name            as actual_center_name             --实际交件转运中心名称
         ,collect_proxy_code            as collect_proxy_code             --交件代理区编码
         ,collect_proxy                 as collect_proxy                  --交件代理区名称
         ,is_diff_proxy                 as is_diff_proxy                  --是否跨代理区交件
         ,early_scan_time               as actual_collect_time            --实际收件时间
         ,actual_send_time              as actual_send_time               --网点实际发件时间
         ,actual_collect_arr_time       as actual_collect_arr_time        --实际交件集散点时间
         ,null                          as plan_collect_arr_time          --规划到达集散点时间
         ,actual_center_arr_time        as actual_center_arr_time         --实际首中心到车时间
         ,null                          as hand_over_latest               --交接最晚到车时间
         ,null                          as is_arr_collect_intime          --到达集散是否准点
         ,null                          as is_arr_center_intime           --到达中心是否准点
         ,null                          as hand_over_intime               --交接是否准点
         ,null                          as collect_responsible_code       --交件不准点责任所属机构编码
         ,null                          as collect_responsible            --交件不准点责任所属机构
         ,null                          as hand_over_responsible_code     --交接不准点责任所属机构编码
         ,null                          as hand_over_responsible          --交接不准点责任所属机构
         ,type                          as type                           --类型,1:无三段码2:无路由3:错发4规划问题5正常6其他
         ,order_source                  as order_source                   --订单来源1紫金山2逍遥峰3桃花岛4其他
         ,goods_type_code               as goods_type_code                --物品类型编码
         ,goods_type_name               as goods_type_name                --物品类型名称
         ,last_network_code             as end_code                       --目的网点编码
         ,last_network_name             as end_name                       --目的网点名称
         ,null                          as plan_arr_date                  --规划到件日期
         ,null                          as plan_center_arr_time           --规划首中心到车时间
         ,null                          as is_over
         ,actual_center_shipment_no     as shipment_no                    --实际首中心到件任务号     (20220903新增)
         ,customer_code                 as customer_code                  --客户编号                 (20220903新增)
         ,null                          as total_days_t                   --T+x时效                  (20220903新增)
         ,null                          as center_planned_departure       --中心规划发车时间-1小时   (20220903新增)
         ,null                          as is_intime                      --时效是否准点             (20220903新增)
         ,express_type_code             as express_type_code              --产品类型编码             (20221103新增)
         ,express_type_name             as express_type_name              --产品类型名称             (20221103新增)
         ,last_area_code                as last_area_code                 --目的区/县编码            (20221113新增)
         ,last_area_name                as last_area_name                 --目的区/县名称            (20221113新增)
         ,last_city_code                as last_city_code                 --目的城市编码             (20221113新增)
         ,last_city_name                as last_city_name                 --目的城市名称             (20221113新增)
         ,last_agent_code               as last_agent_code                --目的代理区编码           (20221113新增)
         ,last_agent_name               as last_agent_name                --目的代理区名称           (20221113新增)
         ,first_nodal_send_time         as first_nodal_send_time          --始发集散发件扫描时间     (20221113新增)
         ,first_nodal_arrival_time      as first_nodal_arrival_time       --始发集散到件扫描时间     (20221113新增)
         ,first_center_send_time        as first_center_send_time         --始发转运中心发件扫描时间 (20221113新增)
         ,first_center_arrival_time     as first_center_arrival_time      --始发转运中心到件扫描时间 (20221113新增)
         ,if_yiqin_late                 as if_yiqin_late                  --是否疫情件               (20221213新增)
         ,null                          as if_route_late                  --路由晚点                 (20221213新增)
         ,null                          as if_center_send_timely          --是否交件晚点及时发出1:是,0:否(20221213新增)
         ,customer_name                 as customer_name                  --客户名称
         ,first_nodal_agent_code        as first_nodal_agent_code         --实际交件集散点代理区编码
         ,first_nodal_agent_name        as first_nodal_agent_name         --实际交件集散点代理区名称
         ,first_nodal_fran_code         as first_nodal_fran_code          --实际交件集散点加盟商编码
         ,first_nodal_fran_name         as first_nodal_fran_name          --实际交件集散点加盟商名称
         ,actual_center_shipment_shifts as shipment_shifts                --实际交件支线任务班次 20230420新增
         ,taking_user_code              as taking_user_code               --揽收业务员 20230516
         ,taking_user_name              as taking_user_name               --揽收业务员 20230516
         ,actual_network_shipment_no    as actual_network_shipment_no     --网点发件任务单
         ,actual_nodal_shipment_no      as actual_nodal_shipment_no       --集散到件发件任务单
         ,first_network_plan_send_time  as first_network_plan_send_time   --始发网点规划发车时间
         ,first_nodal_plan_send_time    as first_nodal_plan_send_time     --始发集散规划发车时间
         ,null                          as hand_over_duty_flag            --不准点责任标识
         ,order_mark_en                 as order_mark_en                  --订单标签英文
         ,order_mark_zh                 as order_mark_zh                  --订单标签中文
         -- ,to_date(early_scan_time)      as dt                             --揽收日期
    from bidefault.dwd_lasted_route_for_submit
    where type = 6   --未到交接时间问题
    union all
    select
          to_date(early_scan_time)      as taking_date                    --揽收日期
         ,waybill_no                    as waybill                        --运单号
         ,is_interrupted                as is_interrupted                 --是否截单0否1是2其他
         ,truncated_time                as truncated_time                 --截单时间
         ,receive_city_id               as receive_city_id                --收件城市id
         ,receive_city                  as receive_city                   --收件城市
         ,region_code                   as region_code                    --收件管理大区编码
         ,region_name                   as region_name                    --收件管理大区名称
         ,receive_proxy_code            as receive_proxy_code             --收件代理区编码
         ,receive_proxy                 as receive_proxy                  --收件代理区名称
         ,receive_franchisee_code       as receive_franchisee_code        --收件加盟商编码
         ,receive_franchisee            as receive_franchisee             --收件加盟商名称
         ,receive_network_code          as receive_network_code           --收件网点编码
         ,receive_network_name          as receive_network_name           --收件网点名称
         ,actual_collect_code           as actual_collect_code            --实际交件集散点编码
         ,actual_collect_name           as actual_collect_name            --实际交件集散点名称
         ,plan_collect_code             as plan_collect_code              --规划到达集散点编码
         ,plan_collect_name             as plan_collect_name              --规划到达集散点名称
         ,actual_center_code            as actual_center_code             --实际交件转运中心编码
         ,actual_center_name            as actual_center_name             --实际交件转运中心名称
         ,collect_proxy_code            as collect_proxy_code             --交件代理区编码
         ,collect_proxy                 as collect_proxy                  --交件代理区名称
         ,is_diff_proxy                 as is_diff_proxy                  --是否跨代理区交件
         ,early_scan_time               as actual_collect_time            --实际收件时间
         ,actual_send_time              as actual_send_time               --网点实际发件时间
         ,actual_collect_arr_time       as actual_collect_arr_time        --实际交件集散点时间
         ,plan_collect_arr_time         as plan_collect_arr_time          --规划到达集散点时间
         ,actual_center_arr_time        as actual_center_arr_time         --实际首中心到车时间
         ,hand_over_latest              as hand_over_latest               --交接最晚到车时间
         ,is_arr_collect_intime         as is_arr_collect_intime          --到达集散是否准点
         ,is_arr_center_intime          as is_arr_center_intime           --到达中心是否准点
         ,hand_over_intime              as hand_over_intime               --交接是否准点
         ,null                          as collect_responsible_code       --交件不准点责任所属机构编码
         ,null                          as collect_responsible            --交件不准点责任所属机构
         ,hand_over_responsible_code    as hand_over_responsible_code     --交接不准点责任所属机构编码
         ,hand_over_responsible         as hand_over_responsible          --交接不准点责任所属机构
         ,type                          as type                           --类型,1:无三段码2:无路由3:错发4规划问题5正常6其他
         ,order_source                  as order_source                   --订单来源1紫金山2逍遥峰3桃花岛4其他
         ,goods_type_code               as goods_type_code                --物品类型编码
         ,goods_type_name               as goods_type_name                --物品类型名称
         ,last_network_code             as end_code                       --目的网点编码
         ,last_network_name             as end_name                       --目的网点名称
         ,plan_arr_date                 as plan_arr_date                  --规划到件日期
         ,plan_center_arr_time          as plan_center_arr_time           --规划首中心到车时间
         ,is_over                       as is_over
         ,actual_center_shipment_no     as shipment_no                    --实际首中心到件任务号     (20220903新增)
         ,customer_code                 as customer_code                  --客户编号                 (20220903新增)
         ,total_days_t                  as total_days_t                   --T+x时效                  (20220903新增)
         ,center_planned_departure      as center_planned_departure       --中心规划发车时间-1小时   (20220903新增)
         ,is_intime                     as is_intime                      --时效是否准点             (20220903新增)
         ,express_type_code             as express_type_code              --产品类型编码             (20221103新增)
         ,express_type_name             as express_type_name              --产品类型名称             (20221103新增)
         ,last_area_code                as last_area_code                 --目的区/县编码            (20221113新增)
         ,last_area_name                as last_area_name                 --目的区/县名称            (20221113新增)
         ,last_city_code                as last_city_code                 --目的城市编码             (20221113新增)
         ,last_city_name                as last_city_name                 --目的城市名称             (20221113新增)
         ,last_agent_code               as last_agent_code                --目的代理区编码           (20221113新增)
         ,last_agent_name               as last_agent_name                --目的代理区名称           (20221113新增)
         ,first_nodal_send_time         as first_nodal_send_time          --始发集散发件扫描时间     (20221113新增)
         ,first_nodal_arrival_time      as first_nodal_arrival_time       --始发集散到件扫描时间     (20221113新增)
         ,first_center_send_time        as first_center_send_time         --始发转运中心发件扫描时间 (20221113新增)
         ,first_center_arrival_time     as first_center_arrival_time      --始发转运中心到件扫描时间 (20221113新增)
         ,if_yiqin_late                 as if_yiqin_late                  --是否疫情件               (20221213新增)
         ,if_route_late                 as if_route_late                  --路由晚点                 (20221213新增)
         ,if_center_send_timely         as if_center_send_timely          --是否交件晚点及时发出1:是,0:否(20221213新增)
         ,customer_name                 as customer_name                  --客户名称
         ,first_nodal_agent_code        as first_nodal_agent_code         --实际交件集散点代理区编码
         ,first_nodal_agent_name        as first_nodal_agent_name         --实际交件集散点代理区名称
         ,first_nodal_fran_code         as first_nodal_fran_code          --实际交件集散点加盟商编码
         ,first_nodal_fran_name         as first_nodal_fran_name          --实际交件集散点加盟商名称
         ,actual_center_shipment_shifts as shipment_shifts                --实际交件支线任务班次 20230420新增
         ,taking_user_code              as taking_user_code               --揽收业务员 20230516
         ,taking_user_name              as taking_user_name               --揽收业务员 20230516
         ,actual_network_shipment_no    as actual_network_shipment_no     --网点发件任务单
         ,actual_nodal_shipment_no      as actual_nodal_shipment_no       --集散到件发件任务单
         ,first_network_plan_send_time  as first_network_plan_send_time   --始发网点规划发车时间
         ,first_nodal_plan_send_time    as first_nodal_plan_send_time     --始发集散规划发车时间
         ,hand_over_duty_flag           as hand_over_duty_flag            --不准点责任标识 0截单后揽收,1有揽未发,2网点发车晚,3网点到集散运输晚,4网点到中心运输晚,5集散发车晚,6集散到中心运输晚
         ,order_mark_en                 as order_mark_en                  --订单标签英文
         ,order_mark_zh                 as order_mark_zh                  --订单标签中文
         -- ,to_date(early_scan_time)      as dt                             --揽收日期
    from bidefault.dwd_result_route_for_submit
    where type = 0 --应交接票数
) a
    left join jms_dim.dim_network_whole_massage dim_network
        on a.receive_network_code = dim_network.code
    left join(
        select
            waybill_no
            ,scan_site_code
            ,scan_time
            ,row_number() over (partition by waybill_no, scan_site_code order by scan_time ) as rn
        from jms_dwd.dwd_tab_barscan_bagging_base_dt
        where dt between date_add('{{ execution_date | cst_ds }}', -1) and '{{ execution_date | cst_ds }}'
    ) bagging
        on a.waybill = bagging.waybill_no
            and a.actual_collect_code = bagging.scan_site_code
            and bagging.rn = 1
where a.receive_proxy_code is not null
distribute by dt,pmod(hash(rand()),100)
;





insert overwrite table bidefault.dm_waybill_collect_intime_taking_agg
select
      taking_date                --揽收日期
     ,receive_city_id            --收件城市id
     ,receive_city               --收件城市
     ,region_code                --收件管理大区编码
     ,region_name                --收件管理大区名称
     ,receive_proxy_code         --收件代理区编码
     ,receive_proxy              --收件代理区名称
     ,receive_franchisee_code    --收件加盟商编码
     ,receive_franchisee         --收件加盟商名称
     ,receive_network_code       --收件网点编码
     ,receive_network_name       --收件网点名称
     ,order_source               --订单来源订单来源1紫金山2逍遥峰3桃花岛4其他
     ,goods_type_code            --物品类型编码
     ,goods_type_name            --物品类型名称
     ,is_interrupted             --是否截单0否1是2其他
     ,three_code_num             --三段码问题:无目的网点
     ,not_route                  --规划问题:无路由
     ,plan_err_num               --规划问题
     ,other                      --其他问题:自收自派
     ,wrong_dispatch             --无路由:错发
     ,collect_code               --交件集散编码
     ,collect_name               --交件集散名称
     ,center_code                --交件转运中心编码
     ,center_name                --交件转运中心名称
     ,null as should_hand_in_num --应交件票数
     ,null as in_time_num        --准点交件票数
     ,null as in_time_rate       --交件准点率
     ,null as not_in_time_num    --不准点交件票数
     ,null as network_resp_num   --交件网点责任票数
     ,null as collect_resp_num   --交件集散责任票数
     ,should_hand_over_num       --应交接票数
     ,hand_over_in_time_num      --准点交接票数
     ,should_hand_over_num - hand_over_in_time_num  as hand_over_not_in_time_num --不准点交接票数
     ,hand_over_network_resp_num --交接网点责任票数
     ,hand_over_collect_resp_num --交接集散责任票数
     ,same_city_num              --同城件票数
     ,unarrival_num              --未到交接时间票数
     ,round(hand_over_in_time_num / should_hand_over_num,2) as hand_over_in_time_rate --交接准点率
     ,not_intime_cnt
     ,express_type_code        as express_type_code       --产品类型编码(20221103新增)
     ,express_type_name        as express_type_name       --产品类型名称(20221103新增)
     ,yiqin_late_cnt           as yiqin_late_cnt          --疫情滞留量
     ,route_late_cnt           as route_late_cnt          --路由晚点量
     ,center_send_timely_cnt   as center_send_timely_cnt  --交件晚点及时发出量
     ,center_send_late_cnt     as center_send_late_cnt    --交件晚点未及时发出量
     ,first_nodal_agent_code   as first_nodal_agent_code  --实际交件集散点代理区编码
     ,first_nodal_agent_name   as first_nodal_agent_name  --实际交件集散点代理区名称
     ,first_nodal_fran_code    as first_nodal_fran_code   --实际交件集散点加盟商编码
     ,first_nodal_fran_name    as first_nodal_fran_name   --实际交件集散点加盟商名称
     ,order_mark_en            as order_mark_en           --订单标签英文
     ,order_mark_zh            as order_mark_zh           --订单标签中文
     ,taking_date as dt
from(
    select
         taking_date              --揽收日期
        ,receive_city_id          --收件城市id
        ,receive_city             --收件城市
        ,region_code              --收件管理大区编码
        ,region_name              --收件管理大区名称
        ,receive_proxy_code       --收件代理区编码
        ,receive_proxy            --收件代理区名称
        ,receive_franchisee_code  --收件加盟商编码
        ,receive_franchisee       --收件加盟商名称
        ,receive_network_code     --收件网点编码
        ,receive_network_name     --收件网点名称
        ,order_source             --订单来源订单来源1紫金山2逍遥峰3桃花岛4其他
        ,goods_type_code          --物品类型编码
        ,goods_type_name          --物品类型名称
        ,is_interrupted           --是否截单0否1是2其他
        ,actual_collect_code     as collect_code        --交件集散编码
        ,actual_collect_name     as collect_name        --交件集散名称
        ,actual_center_code      as center_code         --交件转运中心编码
        ,actual_center_name      as center_name         --交件转运中心名称
        ,express_type_code       as express_type_code   --产品类型编码  (20221103新增)
        ,express_type_name       as express_type_name   --产品类型名称  (20221103新增)
        ,first_nodal_agent_code  first_nodal_agent_code --实际交件集散点代理区编码
        ,first_nodal_agent_name  first_nodal_agent_name --实际交件集散点代理区名称
        ,first_nodal_fran_code   first_nodal_fran_code  --实际交件集散点加盟商编码
        ,first_nodal_fran_name   first_nodal_fran_name  --实际交件集散点加盟商名称
        ,count(case when type = 1 then waybill end) as three_code_num --三段码问题:无目的网点
        ,count(case when type = 2 then waybill end) as not_route      --规划问题:无路由
        ,count(case when type = 2 then waybill end) as plan_err_num   --规划问题
        ,count(case when type = 3 then waybill end) as other          --其他问题:自收自派
        ,count(case when type = 5 then waybill end) as wrong_dispatch --无路由:错发
        ,count(case when type = 4 then waybill end) as same_city_num  --同城件票数
        ,count(case when type = 6 then waybill end) as unarrival_num  --未到交接时间票数
        ,count(case when is_over is not null and type = 0 then waybill end) as should_hand_over_num --应交接票数
        ,count(case when is_over = 1 and type = 0 then waybill end) as hand_over_in_time_num --准点交接票数
        ,count(case when is_over = 0 and type = 0 then waybill end) as hand_over_network_resp_num --交接网点责任票数
        ,count(case when is_over = 2 and type = 0 then waybill end) as hand_over_collect_resp_num --交接集散责任票数
        ,count(case when is_over is not null and type = 0 and is_intime = 0 then waybill end) as not_intime_cnt --时效不准点数
        ,count(case when if_yiqin_late         = 1 then waybill end) as yiqin_late_cnt          --疫情滞留量
        ,count(case when if_route_late         = 1 then waybill end) as route_late_cnt          --路由晚点量
        ,count(case when if_center_send_timely = 1 then waybill end) as center_send_timely_cnt  --交件晚点及时发出量
        ,count(case when if_center_send_timely = 0 then waybill end) as center_send_late_cnt    --交件晚点未及时发出量
        ,order_mark_en                 as order_mark_en                  --订单标签英文
        ,order_mark_zh                 as order_mark_zh                  --订单标签中文
    from bidefault.dm_waybill_collect_intime
    where dt between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
    group by taking_date
            ,receive_city_id
            ,receive_city
            ,region_code
            ,region_name
            ,receive_proxy_code
            ,receive_proxy
            ,receive_franchisee_code
            ,receive_franchisee
            ,receive_network_code
            ,receive_network_name
            ,order_source
            ,goods_type_code
            ,goods_type_name
            ,is_interrupted
            ,actual_collect_code
            ,actual_collect_name
            ,actual_center_code
            ,actual_center_name
            ,express_type_code       --产品类型编码  (20221103新增)
            ,express_type_name       --产品类型名称  (20221103新增)
            ,first_nodal_agent_code  --实际交件集散点代理区编码
            ,first_nodal_agent_name  --实际交件集散点代理区名称
            ,first_nodal_fran_code   --实际交件集散点加盟商编码
            ,first_nodal_fran_name   --实际交件集散点加盟商名称
            ,order_mark_en
            ,order_mark_zh
) a
distribute by dt
;



insert overwrite table bidefault.dm_waybill_collect_intime_summary_dt
select
     main.network_code                as network_code                --交件网点编码
    ,main.network_name                as network_name                --交件网点名称
    ,main.network_fran_code           as network_fran_code           --交件网点加盟商编码
    ,main.network_fran_name           as network_fran_name           --交件网点加盟商名称
    ,main.network_agent_code          as network_agent_code          --交件网点代理区编码
    ,main.network_agent_name          as network_agent_name          --交件网点代理区名称
    ,main.nodal_code                  as nodal_code                  --交件集散编码
    ,main.nodal_name                  as nodal_name                  --交件集散名称
    ,main.nodal_fran_code             as nodal_fran_code             --实际交件集散点加盟商编码
    ,main.nodal_fran_name             as nodal_fran_name             --实际交件集散点加盟商名称
    ,main.nodal_agent_code            as nodal_agent_code            --实际交件集散点代理区编码
    ,main.nodal_agent_name            as nodal_agent_name            --实际交件集散点代理区名称
    ,nvl(main.jj_cnt                    ,0)  as jj_cnt                      --应交件票数
    ,nvl(main.jj_zd_cnt                 ,0)  as jj_zd_cnt                   --准点交件票数
    ,nvl(main.jj_bzd_cnt                ,0)  as jj_bzd_cnt                  --不准点交件票数
    ,nvl(main.jj_network_cnt            ,0)  as jj_network_cnt              --交件网点责任票数
    ,nvl(main.jj_nodal_cnt              ,0)  as jj_nodal_cnt                --交件集散责任票数
    ,nvl(main.effect_qs_cnt             ,0)  as effect_qs_cnt               --时效缺失票数
    ,nvl(main.three_code_cnt            ,0)  as three_code_cnt              --三段码问题票数
    ,nvl(main.error_send_cnt            ,0)  as error_send_cnt              --错发票数
    ,nvl(main.other_cnt                 ,0)  as other_cnt                   --其他票数
    ,nvl(main.local_city_cnt            ,0)  as local_city_cnt              --同城件
    ,nvl(main.weidao_jj_cnt             ,0)  as weidao_jj_cnt               --未到交件时间票数
    ,nvl(main.route_late_cnt            ,0)  as route_late_cnt              --非最优路由票数
    ,nvl(main.center_send_timely_cnt    ,0)  as center_send_timely_cnt      --交件晚点及时发出票数
    ,nvl(main.center_send_late_cnt      ,0)  as center_send_late_cnt        --交件晚点未及时发出票数
    ,nvl(main.effect_bzd_cnt            ,0)  as effect_bzd_cnt              --时效不准点票数
    ,nvl(main.qxt_jj_cnt                ,0)  as qxt_jj_cnt                  --七星潭应交件票数
    ,nvl(main.qxt_jj_zd_cnt             ,0)  as qxt_jj_zd_cnt               --七星潭准点交件票数
    ,nvl(main.qxt_jj_bzd_cnt            ,0)  as qxt_jj_bzd_cnt              --七星潭不准点交件票数
    ,nvl(main.qxt_jj_network_cnt        ,0)  as qxt_jj_network_cnt          --七星潭交件网点责任票数
    ,nvl(main.qxt_jj_nodal_cnt          ,0)  as qxt_jj_nodal_cnt            --七星潭交件集散责任票数
    ,nvl(main.qxt_effect_qs_cnt         ,0)  as qxt_effect_qs_cnt           --七星潭时效缺失票数
    ,nvl(main.qxt_three_code_cnt        ,0)  as qxt_three_code_cnt          --七星潭三段码问题票数
    ,nvl(main.qxt_error_send_cnt        ,0)  as qxt_error_send_cnt          --七星潭错发票数
    ,nvl(main.qxt_other_cnt             ,0)  as qxt_other_cnt               --七星潭其他票数
    ,nvl(main.qxt_local_city_cnt        ,0)  as qxt_local_city_cnt          --七星潭同城件
    ,nvl(main.qxt_weidao_jj_cnt         ,0)  as qxt_weidao_jj_cnt           --七星潭未到交件时间票数
    ,nvl(main.qxt_route_late_cnt        ,0)  as qxt_route_late_cnt          --七星潭非最优路由票数
    ,nvl(main.qxt_center_send_timely_cnt,0)  as qxt_center_send_timely_cnt  --七星潭交件晚点及时发出票数
    ,nvl(main.qxt_center_send_late_cnt  ,0)  as qxt_center_send_late_cnt    --七星潭交件晚点未及时发出票数
    ,nvl(main.qxt_effect_bzd_cnt        ,0)  as qxt_effect_bzd_cnt          --七星潭时效不准点票数
    ,nvl(main.thd_jj_cnt                ,0)  as thd_jj_cnt                  --桃花岛应交件票数
    ,nvl(main.thd_jj_zd_cnt             ,0)  as thd_jj_zd_cnt               --桃花岛准点交件票数
    ,nvl(main.thd_jj_bzd_cnt            ,0)  as thd_jj_bzd_cnt              --桃花岛不准点交件票数
    ,nvl(main.thd_jj_network_cnt        ,0)  as thd_jj_network_cnt          --桃花岛交件网点责任票数
    ,nvl(main.thd_jj_nodal_cnt          ,0)  as thd_jj_nodal_cnt            --桃花岛交件集散责任票数
    ,nvl(main.thd_effect_qs_cnt         ,0)  as thd_effect_qs_cnt           --桃花岛时效缺失票数
    ,nvl(main.thd_three_code_cnt        ,0)  as thd_three_code_cnt          --桃花岛三段码问题票数
    ,nvl(main.thd_error_send_cnt        ,0)  as thd_error_send_cnt          --桃花岛错发票数
    ,nvl(main.thd_other_cnt             ,0)  as thd_other_cnt               --桃花岛其他票数
    ,nvl(main.thd_local_city_cnt        ,0)  as thd_local_city_cnt          --桃花岛同城件
    ,nvl(main.thd_weidao_jj_cnt         ,0)  as thd_weidao_jj_cnt           --桃花岛未到交件时间票数
    ,nvl(main.thd_route_late_cnt        ,0)  as thd_route_late_cnt          --桃花岛非最优路由票数
    ,nvl(main.thd_center_send_timely_cnt,0)  as thd_center_send_timely_cnt  --桃花岛交件晚点及时发出票数
    ,nvl(main.thd_center_send_late_cnt  ,0)  as thd_center_send_late_cnt    --桃花岛交件晚点未及时发出票数
    ,nvl(main.thd_effect_bzd_cnt        ,0)  as thd_effect_bzd_cnt          --桃花岛时效不准点票数
    ,nvl(main.zjs_jj_cnt                ,0)  as zjs_jj_cnt                  --紫金山应交件票数
    ,nvl(main.zjs_jj_zd_cnt             ,0)  as zjs_jj_zd_cnt               --紫金山准点交件票数
    ,nvl(main.zjs_jj_bzd_cnt            ,0)  as zjs_jj_bzd_cnt              --紫金山不准点交件票数
    ,nvl(main.zjs_jj_network_cnt        ,0)  as zjs_jj_network_cnt          --紫金山交件网点责任票数
    ,nvl(main.zjs_jj_nodal_cnt          ,0)  as zjs_jj_nodal_cnt            --紫金山交件集散责任票数
    ,nvl(main.zjs_effect_qs_cnt         ,0)  as zjs_effect_qs_cnt           --紫金山时效缺失票数
    ,nvl(main.zjs_three_code_cnt        ,0)  as zjs_three_code_cnt          --紫金山三段码问题票数
    ,nvl(main.zjs_error_send_cnt        ,0)  as zjs_error_send_cnt          --紫金山错发票数
    ,nvl(main.zjs_other_cnt             ,0)  as zjs_other_cnt               --紫金山其他票数
    ,nvl(main.zjs_local_city_cnt        ,0)  as zjs_local_city_cnt          --紫金山同城件
    ,nvl(main.zjs_weidao_jj_cnt         ,0)  as zjs_weidao_jj_cnt           --紫金山未到交件时间票数
    ,nvl(main.zjs_route_late_cnt        ,0)  as zjs_route_late_cnt          --紫金山非最优路由票数
    ,nvl(main.zjs_center_send_timely_cnt,0)  as zjs_center_send_timely_cnt  --紫金山交件晚点及时发出票数
    ,nvl(main.zjs_center_send_late_cnt  ,0)  as zjs_center_send_late_cnt    --紫金山交件晚点未及时发出票数
    ,nvl(main.zjs_effect_bzd_cnt        ,0)  as zjs_effect_bzd_cnt          --紫金山时效不准点票数
    ,nvl(main.xyf_jj_cnt                ,0)  as xyf_jj_cnt                  --逍遥峰应交件票数
    ,nvl(main.xyf_jj_zd_cnt             ,0)  as xyf_jj_zd_cnt               --逍遥峰准点交件票数
    ,nvl(main.xyf_jj_bzd_cnt            ,0)  as xyf_jj_bzd_cnt              --逍遥峰不准点交件票数
    ,nvl(main.xyf_jj_network_cnt        ,0)  as xyf_jj_network_cnt          --逍遥峰交件网点责任票数
    ,nvl(main.xyf_jj_nodal_cnt          ,0)  as xyf_jj_nodal_cnt            --逍遥峰交件集散责任票数
    ,nvl(main.xyf_effect_qs_cnt         ,0)  as xyf_effect_qs_cnt           --逍遥峰时效缺失票数
    ,nvl(main.xyf_three_code_cnt        ,0)  as xyf_three_code_cnt          --逍遥峰三段码问题票数
    ,nvl(main.xyf_error_send_cnt        ,0)  as xyf_error_send_cnt          --逍遥峰错发票数
    ,nvl(main.xyf_other_cnt             ,0)  as xyf_other_cnt               --逍遥峰其他票数
    ,nvl(main.xyf_local_city_cnt        ,0)  as xyf_local_city_cnt          --逍遥峰同城件
    ,nvl(main.xyf_weidao_jj_cnt         ,0)  as xyf_weidao_jj_cnt           --逍遥峰未到交件时间票数
    ,nvl(main.xyf_route_late_cnt        ,0)  as xyf_route_late_cnt          --逍遥峰非最优路由票数
    ,nvl(main.xyf_center_send_timely_cnt,0)  as xyf_center_send_timely_cnt  --逍遥峰交件晚点及时发出票数
    ,nvl(main.xyf_center_send_late_cnt  ,0)  as xyf_center_send_late_cnt    --逍遥峰交件晚点未及时发出票数
    ,nvl(main.xyf_effect_bzd_cnt        ,0)  as xyf_effect_bzd_cnt          --逍遥峰时效不准点票数
    ,nvl(main.jdw_jj_cnt                ,0)  as jdw_jj_cnt                  --极地湾应交件票数
    ,nvl(main.jdw_jj_zd_cnt             ,0)  as jdw_jj_zd_cnt               --极地湾准点交件票数
    ,nvl(main.jdw_jj_bzd_cnt            ,0)  as jdw_jj_bzd_cnt              --极地湾不准点交件票数
    ,nvl(main.jdw_jj_network_cnt        ,0)  as jdw_jj_network_cnt          --极地湾交件网点责任票数
    ,nvl(main.jdw_jj_nodal_cnt          ,0)  as jdw_jj_nodal_cnt            --极地湾交件集散责任票数
    ,nvl(main.jdw_effect_qs_cnt         ,0)  as jdw_effect_qs_cnt           --极地湾时效缺失票数
    ,nvl(main.jdw_three_code_cnt        ,0)  as jdw_three_code_cnt          --极地湾三段码问题票数
    ,nvl(main.jdw_error_send_cnt        ,0)  as jdw_error_send_cnt          --极地湾错发票数
    ,nvl(main.jdw_other_cnt             ,0)  as jdw_other_cnt               --极地湾其他票数
    ,nvl(main.jdw_local_city_cnt        ,0)  as jdw_local_city_cnt          --极地湾同城件
    ,nvl(main.jdw_weidao_jj_cnt         ,0)  as jdw_weidao_jj_cnt           --极地湾未到交件时间票数
    ,nvl(main.jdw_route_late_cnt        ,0)  as jdw_route_late_cnt          --极地湾非最优路由票数
    ,nvl(main.jdw_center_send_timely_cnt,0)  as jdw_center_send_timely_cnt  --极地湾交件晚点及时发出票数
    ,nvl(main.jdw_center_send_late_cnt  ,0)  as jdw_center_send_late_cnt    --极地湾交件晚点未及时发出票数
    ,nvl(main.jdw_effect_bzd_cnt        ,0)  as jdw_effect_bzd_cnt          --极地湾时效不准点票数
    ,nvl(main.qt_jj_cnt                 ,0)  as qt_jj_cnt                   --其他应交件票数
    ,nvl(main.qt_jj_zd_cnt              ,0)  as qt_jj_zd_cnt                --其他准点交件票数
    ,nvl(main.qt_jj_bzd_cnt             ,0)  as qt_jj_bzd_cnt               --其他不准点交件票数
    ,nvl(main.qt_jj_network_cnt         ,0)  as qt_jj_network_cnt           --其他交件网点责任票数
    ,nvl(main.qt_jj_nodal_cnt           ,0)  as qt_jj_nodal_cnt             --其他交件集散责任票数
    ,nvl(main.qt_effect_qs_cnt          ,0)  as qt_effect_qs_cnt            --其他时效缺失票数
    ,nvl(main.qt_three_code_cnt         ,0)  as qt_three_code_cnt           --其他三段码问题票数
    ,nvl(main.qt_error_send_cnt         ,0)  as qt_error_send_cnt           --其他错发票数
    ,nvl(main.qt_other_cnt              ,0)  as qt_other_cnt                --其他其他票数
    ,nvl(main.qt_local_city_cnt         ,0)  as qt_local_city_cnt           --其他同城件
    ,nvl(main.qt_weidao_jj_cnt          ,0)  as qt_weidao_jj_cnt            --其他未到交件时间票数
    ,nvl(main.qt_route_late_cnt         ,0)  as qt_route_late_cnt           --其他非最优路由票数
    ,nvl(main.qt_center_send_timely_cnt ,0)  as qt_center_send_timely_cnt   --其他交件晚点及时发出票数
    ,nvl(main.qt_center_send_late_cnt   ,0)  as qt_center_send_late_cnt     --其他交件晚点未及时发出票数
    ,nvl(main.qt_effect_bzd_cnt         ,0)  as qt_effect_bzd_cnt           --其他时效不准点票数
    ,case when main.dt between dim.create_date and dim.end_date then '新开网点'
          when main.dt between event.happen_time and event.end_time then '时效顺延'
          when main.dt between inter.effect_time_start and inter.effect_time_end then '筛单拦截'
          end as exempt_type  --豁免类型
    ,case when main.dt between dim.create_date and dim.end_date then null
          when main.dt between event.happen_time and event.end_time then null
          when main.dt between inter.effect_time_start and inter.effect_time_end then null
          else (main.jj_network_cnt - main.jdw_jj_network_cnt - main.qt_jj_network_cnt ) * config.fee_per end as fine_amt  --豁免类型
    ,dim.virt_code         -- 虚拟代理区code
    ,dim.virt_name         -- 虚拟代理区名称
    ,dim.zone_code         -- 片区code
    ,dim.zone_name         -- 片区名称
    ,dim.zone_staff_code   -- 片区负责人code
    ,dim.zone_staff_name   -- 片区负责人名称
    ,dim.area_code         -- 底层片区
    ,dim.area_name         -- 底层片区
    ,main.dt
from (
    select
         receive_network_code    as network_code         --交件网点编码
        ,receive_network_name    as network_name         --交件网点名称
        ,receive_franchisee_code as network_fran_code    --交件网点加盟商编码
        ,receive_franchisee      as network_fran_name    --交件网点加盟商名称
        ,receive_proxy_code      as network_agent_code   --交件网点代理区编码
        ,receive_proxy           as network_agent_name   --交件网点代理区名称
        ,collect_code            as nodal_code           --交件集散编码
        ,collect_name            as nodal_name           --交件集散名称
        ,first_nodal_fran_code   as nodal_fran_code      --实际交件集散点加盟商编码
        ,first_nodal_fran_name   as nodal_fran_name      --实际交件集散点加盟商名称
        ,first_nodal_agent_code  as nodal_agent_code     --实际交件集散点代理区编码
        ,first_nodal_agent_name  as nodal_agent_name     --实际交件集散点代理区名称
        ,nvl(sum(should_hand_over_num      ),0) as jj_cnt                 --应交件票数
        ,nvl(sum(hand_over_in_time_num     ),0) as jj_zd_cnt              --准点交件票数
        ,nvl(sum(hand_over_not_in_time_num ),0) as jj_bzd_cnt             --不准点交件票数
        ,nvl(sum(hand_over_network_resp_num),0) as jj_network_cnt         --交件网点责任票数
        ,nvl(sum(hand_over_collect_resp_num),0) as jj_nodal_cnt           --交件集散责任票数
        ,nvl(sum(plan_err_num              ),0) as effect_qs_cnt          --时效缺失票数
        ,nvl(sum(three_code_num            ),0) as three_code_cnt         --三段码问题票数
        ,nvl(sum(wrong_dispatch            ),0) as error_send_cnt         --错发票数
        ,nvl(sum(other                     ),0) as other_cnt              --其他票数
        ,nvl(sum(same_city_num             ),0) as local_city_cnt         --同城件
        ,nvl(sum(unarrival_num             ),0) as weidao_jj_cnt          --未到交件时间票数
        ,nvl(sum(route_late_cnt            ),0) as route_late_cnt         --非最优路由票数
        ,nvl(sum(center_send_timely_cnt    ),0) as center_send_timely_cnt --交件晚点及时发出票数
        ,nvl(sum(center_send_late_cnt      ),0) as center_send_late_cnt   --交件晚点未及时发出票数
        ,nvl(sum(not_intime_cnt            ),0) as effect_bzd_cnt         --时效不准点票数
        --七星潭
        ,nvl(sum(case when order_source='七星潭' then should_hand_over_num       end),0) as qxt_jj_cnt                 --应交件票数
        ,nvl(sum(case when order_source='七星潭' then hand_over_in_time_num      end),0) as qxt_jj_zd_cnt              --准点交件票数
        ,nvl(sum(case when order_source='七星潭' then hand_over_not_in_time_num  end),0) as qxt_jj_bzd_cnt             --不准点交件票数
        ,nvl(sum(case when order_source='七星潭' then hand_over_network_resp_num end),0) as qxt_jj_network_cnt         --交件网点责任票数
        ,nvl(sum(case when order_source='七星潭' then hand_over_collect_resp_num end),0) as qxt_jj_nodal_cnt           --交件集散责任票数
        ,nvl(sum(case when order_source='七星潭' then plan_err_num               end),0) as qxt_effect_qs_cnt          --时效缺失票数
        ,nvl(sum(case when order_source='七星潭' then three_code_num             end),0) as qxt_three_code_cnt         --三段码问题票数
        ,nvl(sum(case when order_source='七星潭' then wrong_dispatch             end),0) as qxt_error_send_cnt         --错发票数
        ,nvl(sum(case when order_source='七星潭' then other                      end),0) as qxt_other_cnt              --其他票数
        ,nvl(sum(case when order_source='七星潭' then same_city_num              end),0) as qxt_local_city_cnt         --同城件
        ,nvl(sum(case when order_source='七星潭' then unarrival_num              end),0) as qxt_weidao_jj_cnt          --未到交件时间票数
        ,nvl(sum(case when order_source='七星潭' then route_late_cnt             end),0) as qxt_route_late_cnt         --非最优路由票数
        ,nvl(sum(case when order_source='七星潭' then center_send_timely_cnt     end),0) as qxt_center_send_timely_cnt --交件晚点及时发出票数
        ,nvl(sum(case when order_source='七星潭' then center_send_late_cnt       end),0) as qxt_center_send_late_cnt   --交件晚点未及时发出票数
        ,nvl(sum(case when order_source='七星潭' then not_intime_cnt             end),0) as qxt_effect_bzd_cnt         --时效不准点票数
        --桃花岛
        ,nvl(sum(case when order_source='桃花岛' then should_hand_over_num       end),0) as thd_jj_cnt                 --应交件票数
        ,nvl(sum(case when order_source='桃花岛' then hand_over_in_time_num      end),0) as thd_jj_zd_cnt              --准点交件票数
        ,nvl(sum(case when order_source='桃花岛' then hand_over_not_in_time_num  end),0) as thd_jj_bzd_cnt             --不准点交件票数
        ,nvl(sum(case when order_source='桃花岛' then hand_over_network_resp_num end),0) as thd_jj_network_cnt         --交件网点责任票数
        ,nvl(sum(case when order_source='桃花岛' then hand_over_collect_resp_num end),0) as thd_jj_nodal_cnt           --交件集散责任票数
        ,nvl(sum(case when order_source='桃花岛' then plan_err_num               end),0) as thd_effect_qs_cnt          --时效缺失票数
        ,nvl(sum(case when order_source='桃花岛' then three_code_num             end),0) as thd_three_code_cnt         --三段码问题票数
        ,nvl(sum(case when order_source='桃花岛' then wrong_dispatch             end),0) as thd_error_send_cnt         --错发票数
        ,nvl(sum(case when order_source='桃花岛' then other                      end),0) as thd_other_cnt              --其他票数
        ,nvl(sum(case when order_source='桃花岛' then same_city_num              end),0) as thd_local_city_cnt         --同城件
        ,nvl(sum(case when order_source='桃花岛' then unarrival_num              end),0) as thd_weidao_jj_cnt          --未到交件时间票数
        ,nvl(sum(case when order_source='桃花岛' then route_late_cnt             end),0) as thd_route_late_cnt         --非最优路由票数
        ,nvl(sum(case when order_source='桃花岛' then center_send_timely_cnt     end),0) as thd_center_send_timely_cnt --交件晚点及时发出票数
        ,nvl(sum(case when order_source='桃花岛' then center_send_late_cnt       end),0) as thd_center_send_late_cnt   --交件晚点未及时发出票数
        ,nvl(sum(case when order_source='桃花岛' then not_intime_cnt             end),0) as thd_effect_bzd_cnt         --时效不准点票数
        --紫金山
        ,nvl(sum(case when order_source='紫金山' then should_hand_over_num       end),0) as zjs_jj_cnt                 --应交件票数
        ,nvl(sum(case when order_source='紫金山' then hand_over_in_time_num      end),0) as zjs_jj_zd_cnt              --准点交件票数
        ,nvl(sum(case when order_source='紫金山' then hand_over_not_in_time_num  end),0) as zjs_jj_bzd_cnt             --不准点交件票数
        ,nvl(sum(case when order_source='紫金山' then hand_over_network_resp_num end),0) as zjs_jj_network_cnt         --交件网点责任票数
        ,nvl(sum(case when order_source='紫金山' then hand_over_collect_resp_num end),0) as zjs_jj_nodal_cnt           --交件集散责任票数
        ,nvl(sum(case when order_source='紫金山' then plan_err_num               end),0) as zjs_effect_qs_cnt          --时效缺失票数
        ,nvl(sum(case when order_source='紫金山' then three_code_num             end),0) as zjs_three_code_cnt         --三段码问题票数
        ,nvl(sum(case when order_source='紫金山' then wrong_dispatch             end),0) as zjs_error_send_cnt         --错发票数
        ,nvl(sum(case when order_source='紫金山' then other                      end),0) as zjs_other_cnt              --其他票数
        ,nvl(sum(case when order_source='紫金山' then same_city_num              end),0) as zjs_local_city_cnt         --同城件
        ,nvl(sum(case when order_source='紫金山' then unarrival_num              end),0) as zjs_weidao_jj_cnt          --未到交件时间票数
        ,nvl(sum(case when order_source='紫金山' then route_late_cnt             end),0) as zjs_route_late_cnt         --非最优路由票数
        ,nvl(sum(case when order_source='紫金山' then center_send_timely_cnt     end),0) as zjs_center_send_timely_cnt --交件晚点及时发出票数
        ,nvl(sum(case when order_source='紫金山' then center_send_late_cnt       end),0) as zjs_center_send_late_cnt   --交件晚点未及时发出票数
        ,nvl(sum(case when order_source='紫金山' then not_intime_cnt             end),0) as zjs_effect_bzd_cnt         --时效不准点票数
        --逍遥峰
        ,nvl(sum(case when order_source='逍遥峰' then should_hand_over_num       end),0) as xyf_jj_cnt                 --应交件票数
        ,nvl(sum(case when order_source='逍遥峰' then hand_over_in_time_num      end),0) as xyf_jj_zd_cnt              --准点交件票数
        ,nvl(sum(case when order_source='逍遥峰' then hand_over_not_in_time_num  end),0) as xyf_jj_bzd_cnt             --不准点交件票数
        ,nvl(sum(case when order_source='逍遥峰' then hand_over_network_resp_num end),0) as xyf_jj_network_cnt         --交件网点责任票数
        ,nvl(sum(case when order_source='逍遥峰' then hand_over_collect_resp_num end),0) as xyf_jj_nodal_cnt           --交件集散责任票数
        ,nvl(sum(case when order_source='逍遥峰' then plan_err_num               end),0) as xyf_effect_qs_cnt          --时效缺失票数
        ,nvl(sum(case when order_source='逍遥峰' then three_code_num             end),0) as xyf_three_code_cnt         --三段码问题票数
        ,nvl(sum(case when order_source='逍遥峰' then wrong_dispatch             end),0) as xyf_error_send_cnt         --错发票数
        ,nvl(sum(case when order_source='逍遥峰' then other                      end),0) as xyf_other_cnt              --其他票数
        ,nvl(sum(case when order_source='逍遥峰' then same_city_num              end),0) as xyf_local_city_cnt         --同城件
        ,nvl(sum(case when order_source='逍遥峰' then unarrival_num              end),0) as xyf_weidao_jj_cnt          --未到交件时间票数
        ,nvl(sum(case when order_source='逍遥峰' then route_late_cnt             end),0) as xyf_route_late_cnt         --非最优路由票数
        ,nvl(sum(case when order_source='逍遥峰' then center_send_timely_cnt     end),0) as xyf_center_send_timely_cnt --交件晚点及时发出票数
        ,nvl(sum(case when order_source='逍遥峰' then center_send_late_cnt       end),0) as xyf_center_send_late_cnt   --交件晚点未及时发出票数
        ,nvl(sum(case when order_source='逍遥峰' then not_intime_cnt             end),0) as xyf_effect_bzd_cnt         --时效不准点票数
        --极地湾
        ,nvl(sum(case when order_source='极地湾' then should_hand_over_num       end),0) as jdw_jj_cnt                 --应交件票数
        ,nvl(sum(case when order_source='极地湾' then hand_over_in_time_num      end),0) as jdw_jj_zd_cnt              --准点交件票数
        ,nvl(sum(case when order_source='极地湾' then hand_over_not_in_time_num  end),0) as jdw_jj_bzd_cnt             --不准点交件票数
        ,nvl(sum(case when order_source='极地湾' then hand_over_network_resp_num end),0) as jdw_jj_network_cnt         --交件网点责任票数
        ,nvl(sum(case when order_source='极地湾' then hand_over_collect_resp_num end),0) as jdw_jj_nodal_cnt           --交件集散责任票数
        ,nvl(sum(case when order_source='极地湾' then plan_err_num               end),0) as jdw_effect_qs_cnt          --时效缺失票数
        ,nvl(sum(case when order_source='极地湾' then three_code_num             end),0) as jdw_three_code_cnt         --三段码问题票数
        ,nvl(sum(case when order_source='极地湾' then wrong_dispatch             end),0) as jdw_error_send_cnt         --错发票数
        ,nvl(sum(case when order_source='极地湾' then other                      end),0) as jdw_other_cnt              --其他票数
        ,nvl(sum(case when order_source='极地湾' then same_city_num              end),0) as jdw_local_city_cnt         --同城件
        ,nvl(sum(case when order_source='极地湾' then unarrival_num              end),0) as jdw_weidao_jj_cnt          --未到交件时间票数
        ,nvl(sum(case when order_source='极地湾' then route_late_cnt             end),0) as jdw_route_late_cnt         --非最优路由票数
        ,nvl(sum(case when order_source='极地湾' then center_send_timely_cnt     end),0) as jdw_center_send_timely_cnt --交件晚点及时发出票数
        ,nvl(sum(case when order_source='极地湾' then center_send_late_cnt       end),0) as jdw_center_send_late_cnt   --交件晚点未及时发出票数
        ,nvl(sum(case when order_source='极地湾' then not_intime_cnt             end),0) as jdw_effect_bzd_cnt         --时效不准点票数
        --其他
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then should_hand_over_num       end),0) as qt_jj_cnt                 --应交件票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then hand_over_in_time_num      end),0) as qt_jj_zd_cnt              --准点交件票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then hand_over_not_in_time_num  end),0) as qt_jj_bzd_cnt             --不准点交件票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then hand_over_network_resp_num end),0) as qt_jj_network_cnt         --交件网点责任票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then hand_over_collect_resp_num end),0) as qt_jj_nodal_cnt           --交件集散责任票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then plan_err_num               end),0) as qt_effect_qs_cnt          --时效缺失票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then three_code_num             end),0) as qt_three_code_cnt         --三段码问题票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then wrong_dispatch             end),0) as qt_error_send_cnt         --错发票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then other                      end),0) as qt_other_cnt              --其他票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then same_city_num              end),0) as qt_local_city_cnt         --同城件
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then unarrival_num              end),0) as qt_weidao_jj_cnt          --未到交件时间票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then route_late_cnt             end),0) as qt_route_late_cnt         --非最优路由票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then center_send_timely_cnt     end),0) as qt_center_send_timely_cnt --交件晚点及时发出票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then center_send_late_cnt       end),0) as qt_center_send_late_cnt   --交件晚点未及时发出票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then not_intime_cnt             end),0) as qt_effect_bzd_cnt         --时效不准点票数
        ,'交件准点率汇总' as fee_menu
        ,dt
    from bidefault.dm_waybill_collect_intime_taking_agg
    where dt between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
    group by receive_network_code      --收件网点编码
            ,receive_network_name      --收件网点名称
            ,receive_franchisee_code   --收件网点加盟商编码
            ,receive_franchisee        --收件网点加盟商名称
            ,receive_proxy_code        --收件网点代理区编码
            ,receive_proxy             --收件网点代理区名称
            ,collect_code              --交件集散编码
            ,collect_name              --交件集散名称
            ,first_nodal_fran_code     --实际交件集散点加盟商编码
            ,first_nodal_fran_name     --实际交件集散点加盟商名称
            ,first_nodal_agent_code    --实际交件集散点代理区编码
            ,first_nodal_agent_name    --实际交件集散点代理区名称
            ,dt
) main
left join (
    select
    fee_per,fee_menu
    from jms_dim.dim_tab_forfeit_config_new
    where statue = 1
    and is_delete = 1
    and date_sub('{{ execution_date | cst_ds }}',1) between fee_start_time and fee_end_time
    and fee_menu = '交件准点率汇总'
) config on config.fee_menu = main.fee_menu
left join (
    select
         code
        ,name
        ,create_time
        ,to_date(create_time) as create_date
        ,date_add(to_date(create_time),15)  as end_date
        ,virt_code         -- 虚拟代理区code
        ,virt_name         -- 虚拟代理区名称
        ,zone_code         -- 片区code
        ,zone_name         -- 片区名称
        ,zone_staff_code   -- 片区负责人code
        ,zone_staff_name   -- 片区负责人名称
        ,area_code         -- 底层片区
        ,area_name         -- 底层片区
    from jms_dim.dim_network_whole_massage  --网点资料，豁免开往+15天
) dim on main.network_code = dim.code
left join (
    select
         involved_org_code as involved_org_code --涉事组织编码
        ,involved_org_name as involved_org_name --涉事组织名称
        ,max(report_time)  as report_time       --上报时间
        ,to_date(max(happen_time)) as happen_time  --发生时间
        ,to_date(max(end_time   )) as end_time     --结束时间
    from jms_dim.dim_abnormal_event_report_base --异常事件报表
    where to_date(happen_time) between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
    group by involved_org_code   --涉事组织编码
            ,involved_org_name   --涉事组织名称
) event on main.network_code = event.involved_org_code
left join (
    select
         network_code       --网点编码
        ,network_name       --网点名称
        ,to_date(effect_time_start) as effect_time_start --开始时间
        ,date_add(to_date(effect_time_end),5) as effect_time_end  --结束时间
    from jms_dm.dm_waybill_collect_intime_interceptor_base
) inter on inter.network_code = main.network_code
distribute by pmod(hash(rand()),10);




insert overwrite table bidefault.dm_waybill_collect_intime_diagnostic_summary partition(dt)
select
     waybill.taking_date
    ,waybill.receive_network_code
    ,waybill.receive_network_name
    ,waybill.receive_franchisee_code
    ,waybill.receive_franchisee
    ,waybill.receive_proxy_code
    ,waybill.receive_proxy
    ,waybill.actual_collect_code         as collect_code
    ,waybill.actual_collect_name         as collect_name
    ,waybill.actual_center_code          as center_code
    ,waybill.actual_center_name          as center_name
    ,waybill.actual_network_shipment_no
    ,waybill.taking_user_code
    ,waybill.taking_user_name
    ,waybill.customer_code
    ,waybill.customer_name
    ,waybill.type       -- 是否应交件
    ,waybill.is_over    -- 是否准点
    ,count(case when waybill.is_over is not null and waybill.type = 0 then waybill end)                          as should_hand_over_num                      -- 应交件票数
    ,count(case when waybill.is_over = 1         and waybill.type = 0 then waybill end)                          as hand_over_in_time_num                     -- 准点交件票数
    ,count(case when waybill.is_over <> 1        and waybill.type = 0 then waybill end)                          as hand_over_not_in_time_num                 -- 不准点交件票数
    ,count(case when waybill.is_over = 0 and waybill.type = 0 then waybill end)                                  as hand_over_network_resp_num                -- 交接网点责任票数
    ,count(case when waybill.is_over = 2 and waybill.type = 0 then waybill end)                                  as hand_over_collect_resp_num                -- 交接集散责任票数
    ,count(case when waybill.is_over is not null and waybill.type = 0 and waybill.is_arr_collect_intime = 0 then waybill end)  as collect_arr_late_cnt        -- 到集散不准点
    ,count(case when waybill.is_over <> 1        and waybill.type = 0 then waybill end)                          as center_arr_late_cnt                       -- 到中心不准点（现在和不准点交件票数逻辑相同）
    ,count(case when waybill.is_interrupted = 1  and waybill.type = 0 then waybill end)                          as interrupted_taking_cnt                    -- 截单后揽收量
    ,count(case when coalesce(waybill.actual_send_time, waybill.actual_collect_arr_time,
                              waybill.first_nodal_send_time , waybill.actual_center_arr_time, waybill.first_center_send_time)
                     is null then 1 end)                                                                         as taking_no_send_cnt                        -- 有揽未发量
    ,count(case when
                    nvl(actual_send_time, date_add('{{ execution_date | cst_ds }}', 1) || ' 07:00:00')
                    > first_network_plan_send_time
                    then waybill
            end)                                                                                                 as network_send_late_cnt                     -- 网点发车晚量
    ,count(case when actual_collect_code is not null
                        and nvl(actual_collect_arr_time, date_add('{{ execution_date | cst_ds }}', 1)|| ' 07:00:00') > plan_collect_arr_time
                     then waybill
           end)                                                                                                  as network_collect_transport_late_cnt        -- 网点集散运输晚数量

    ,count(case when actual_collect_code is null
                     and nvl(actual_center_arr_time, date_add('{{ execution_date | cst_ds }}', 1) || ' 07:00:00') > plan_center_arr_time
                     then waybill
           end)                                                                                                  as network_center_transport_late_cnt         -- 网点中心运输晚数量
    ,count(case when actual_collect_code is not null
                     and nvl(first_nodal_send_time,  date_add('{{ execution_date | cst_ds }}', 1) || ' 07:00:00')
                     > first_nodal_plan_send_time
                     then waybill
           end)                                                                                                  as collect_send_late_cnt                     -- 集散发车晚数量
    ,count(case when actual_collect_code is not null
                     and nvl(actual_center_arr_time, date_add('{{ execution_date | cst_ds }}', 1) || ' 07:00:00')
                     > plan_center_arr_time
                     then waybill
          end)                                                                                                   as collect_center_transport_late_cnt         -- 集散中心运输晚数量
    ,count(case when waybill.if_center_send_timely = 1 then waybill end)                                         as center_send_timely_cnt                    -- 交件晚点及时发出量
    ,count(case when waybill.if_center_send_timely = 0 then waybill end)                                         as center_send_late_cnt                      -- 交件晚点未及时发出量
    ,nvl(count(false_taking.waybill_no),0)                                                                       as false_taking_cnt                          -- 虚假揽收数量
    ,waybill.area_code
    ,waybill.area_name
    ,dim_network.zone_code
    ,dim_network.zone_name
    ,count(actual_collect_time)                                                                                  as network_taking_cnt     -- 网点揽收数量
    ,count(actual_send_time)                                                                                     as network_send_cnt       -- 网点发件数量
    ,count(actual_collect_arr_time)                                                                              as collect_arr_cnt        -- 集散到件数量
    ,count(first_nodal_send_time)                                                                                as collect_send_cnt       -- 集散到件数量
    ,count(actual_center_arr_time)                                                                               as center_arr_cnt         -- 中心到件数量
    ,count(first_center_send_time)                                                                               as center_send_cnt        -- 中心发件数量
    ,waybill.dt
from bidefault.dm_waybill_collect_intime waybill
left join
        jms_dm.dm_waybill_false_taking_dt false_taking
            on waybill.waybill = false_taking.waybill_no
               and false_taking.dt between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
left join
        jms_dim.dim_network_whole_massage dim_network
            on waybill.receive_network_code = dim_network.code
where waybill.dt between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
group by
     waybill.receive_network_code
    ,waybill.receive_network_name
    ,waybill.receive_proxy_code
    ,waybill.receive_proxy
    ,waybill.receive_franchisee_code
    ,waybill.receive_franchisee
    ,waybill.actual_collect_code
    ,waybill.actual_collect_name
    ,waybill.actual_center_code
    ,waybill.actual_center_name
    ,waybill.actual_network_shipment_no
    ,waybill.taking_user_code
    ,waybill.taking_user_name
    ,waybill.customer_code
    ,waybill.customer_name
    ,waybill.taking_date
    ,waybill.area_code
    ,waybill.area_name
    ,waybill.type
    ,waybill.is_over
    ,dim_network.zone_code
    ,dim_network.zone_name
    ,waybill.dt
distribute by waybill.dt
;
